/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BancoDeDados;

import Biblioteca.VerificacaoErros;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author Igor
 */
public class GerenciarOperacoes {
    //Atributos
    private ConexaoFirebird con = null;
    private Statement select = null;
    private ArrayList<String> tabelas = null;
    private ArrayList<String> atributos = null;
    private ArrayList<String> constraintNames = null;
    
    //Construtores
    public GerenciarOperacoes(){
        try{
            this.con = ConexaoFirebird.getInstance();//new ConexaoFirebird();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        this.tabelas = new ArrayList<>();
        this.atributos = new ArrayList<>();
        this.constraintNames = new ArrayList();
    }
    
    //Outros Metodos
    public void executaQuery(String SQL){
        try{
            this.con.startDB();
            this.select = con.getStm();
            this.select.executeUpdate(SQL);
            this.con.stopDB();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    public void criaPrimaryKey(String tableName, String constraintName, String fieldsNames){
        this.executaQuery("ALTER TABLE "+tableName+" ADD CONSTRAINT "+constraintName+" PRIMARY KEY ("+fieldsNames+");");
    }
    
    public void criaForeignKey(String tableOriginName, String constraintName, String fieldsOriginNames,
            String tableDestinationNames, String fieldsDestinationNames){
        this.executaQuery("ALTER TABLE "+tableOriginName+" ADD CONSTRAINT "+constraintName+" FOREIGN KEY ("
                +fieldsOriginNames+") REFERENCES "+tableDestinationNames+" ("+fieldsDestinationNames+");");
    }
    
    public ArrayList<String> selectTabelas(){
        ResultSet result = null;

        try{
            this.con.startDB();
            this.select = con.getStm();
            result = this.select.executeQuery("select trim(RDB$RELATION_NAME) as TABLES from RDB$RELATIONS where RDB$RELATION_NAME not like '%$%';");
            while(result.next()){
                this.tabelas.add(result.getString("TABLES"));
            }
            this.con.stopDB();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        return this.tabelas;
    }
    
    public Boolean localizaTabelas(String tabelas){
        for(int i = 0;i < this.tabelas.size();i++){
            if(this.tabelas.get(i).equals(tabelas)){
                return true;
            }
        }
        return false;
    }
    
    public ArrayList<String> selectCampos(String tabela){
        ResultSet result = null;
        String str_SQL = "";

        try{
            this.con.startDB();
            this.select = con.getStm();
            str_SQL = "SELECT trim(r.RDB$FIELD_NAME) AS field_name,\n" 
                    + "trim(r.RDB$DESCRIPTION) AS field_description,\n"
                    + "trim(r.RDB$DEFAULT_VALUE) AS field_default_value,\n"
                    + "trim(r.RDB$NULL_FLAG) AS field_not_null_constraint,\n"
                    + "trim(f.RDB$FIELD_LENGTH) AS field_length,\n"
                    + "trim(f.RDB$FIELD_PRECISION) AS field_precision,\n"
                    + "trim(f.RDB$FIELD_SCALE) AS field_scale,\n"
                    + "CASE f.RDB$FIELD_TYPE\n"
                    + "WHEN 261 THEN trim('BLOB')\n"
                    + "WHEN 14 THEN trim('CHAR')\n"
                    + "WHEN 40 THEN trim('CSTRING')\n"
                    + "WHEN 11 THEN trim('D_FLOAT')\n"
                    + "WHEN 27 THEN trim('DOUBLE')\n"
                    + "WHEN 10 THEN trim('FLOAT')\n"
                    + "WHEN 16 THEN trim('NUMERIC')\n"
                    + "WHEN 8 THEN trim('INTEGER')\n"
                    + "WHEN 9 THEN trim('QUAD')\n"
                    + "WHEN 7 THEN trim('SMALLINT')\n"
                    + "WHEN 12 THEN trim('DATE')\n"
                    + "WHEN 13 THEN trim('TIME')\n"
                    + "WHEN 35 THEN trim('TIMESTAMP')\n"
                    + "WHEN 37 THEN trim('VARCHAR')\n"
                    + "ELSE trim('UNKNOWN')\n"
                    + "END AS field_type,\n"
                    + "f.RDB$FIELD_SUB_TYPE AS field_subtype,\n"
                    + "coll.RDB$COLLATION_NAME AS field_collation,\n"
                    + "cset.RDB$CHARACTER_SET_NAME AS field_charset \n"
                    + "FROM RDB$RELATION_FIELDS r \n"
                    + "LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME \n"
                    + "LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID \n"
                    + "LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID \n"
                    + "WHERE r.RDB$RELATION_NAME = '"+ tabela +"'\n"
                    + "ORDER BY r.RDB$FIELD_POSITION";
            
            result = this.select.executeQuery(str_SQL);
            while(result.next()){
                this.atributos.add(result.getString("field_name"));
            }
            this.con.stopDB();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        return this.atributos;
    }
    
    public Boolean localizaAtributo(String atributo){
        for(int i = 0;i < this.atributos.size();i++){
            if(this.atributos.get(i).equals(atributo)){
                return true;
            }
        }
        return false;
    }
    
    public ArrayList<String> selectConstraintNames(String tabela){
        ResultSet result = null;
        String str_SQL = "";

        try{
            this.con.startDB();
            this.select = con.getStm();
            str_SQL = "select distinct trim(tc.RDB$CONSTRAINT_NAME) as CONSTRAINT_NAME\n" +
                      "from RDB$RELATION_CONSTRAINTS tc\n" +
                      "join RDB$INDEX_SEGMENTS idx\n" +
                      "on (idx.RDB$INDEX_NAME = tc.RDB$INDEX_NAME)\n" +
                      "where tc.RDB$RELATION_NAME = '"+tabela+"'\n" +
                      "order by idx.RDB$FIELD_POSITION";
            
            result = this.select.executeQuery(str_SQL);
            while(result.next()){
                this.constraintNames.add(result.getString("CONSTRAINT_NAME"));
            }
            
            this.con.stopDB();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        return this.constraintNames;
    }
    
    public Boolean localizaConstraintNames(String constraintNames){
        for(int i = 0;i < this.constraintNames.size();i++){
            if(this.constraintNames.get(i).equals(constraintNames)){
                return true;
            }
        }
        return false;
    }
}
